<HTML><HEAD><TITLE>session_sql_prepare_query(++Session, +ParamTemplate, +ResultTemplate, ++SQLQuery, -Cursor)</TITLE>
</HEAD><BODY>[ <A HREF="index.html">library(dbi)</A> | <A HREF="../../index.html">Reference Manual</A> | <A HREF="../../fullindex.html">Alphabetic Index</A> ]
<H1>session_sql_prepare_query(++Session, +ParamTemplate, +ResultTemplate, ++SQLQuery, -Cursor)</H1>
Prepares a SQL query for execution by the DBMS.
<DL>
<DT><EM>Session</EM></DT>
<DD>A session handle
</DD>
<DT><EM>ParamTemplate</EM></DT>
<DD>Template defining the types of the parameters (structure or [])
</DD>
<DT><EM>ResultTemplate</EM></DT>
<DD>Template defining the types of results tuple (structure)
</DD>
<DT><EM>SQLQuery</EM></DT>
<DD>A SQL query in prepared syntax (string)
</DD>
<DT><EM>Cursor</EM></DT>
<DD>Returned cursor handle
</DD>
</DL>
<H2>Description</H2>
<P>
 Prepares a SQL query for execution. The query is not actually
 executed, and a cursor_next_execute/2 needs to be called to execute the 
 SQL query, giving values to any parameters. Then the cursor_*_tuple family
 of predicates can be used to obtain the results. This facility is only
 available if the DBMS supports prepared statements, and the SQL query has
 to be written in the prepared statement syntax of the DBMS. The predicate
 returns the cursor handle representing this prepared query in Cursor,
 which can then be used in subsequent library predicates.
</P><P>
 A prepared SQL statement is parsed by the DBMS, so that it could be
 executed more efficiently. It can also be parameterised, where the 
 parameters represent values to be filled in when the statement is
 executed. The statement can be executed multiple times with different
 parameters. The types of the parameters is specified by ParamTemplate,
 which is a Prolog structure of arity M (where M is the number of
 parameters for the statement), or the nil atom [] if there are no parameters.
 See the general description of this library or the manual for a
 description of the template specification.
</P><P>
 The SQL query returns result in tuples of N elements each. Each tuple is
 mapped to a Prolog structure of arity N. ResultTemplate is a structure of
 arity N specifying the types of the return data for ECLiPSe. See the
 general description of this library or the manual for a description of 
 the template specification.
</P><P>
 The SQL query must be valid for the DBMS to execute. It can contain
 NULL characters, i.e. it can contain binary data.
</P><P>
 Note that some DBMS restricts which SQL statements can be prepared. If an
 SQL statement cannot be prepared, it can still be executed using
 session_sql/3.
</P><P>
 MySQL specific note: not all SQL statements can be prepared by MySQL.
 Refer to the MySQL manual for details.

<H3>Exceptions</H3>
<DL>
<DT><EM>(5) type error </EM>
<DD>Session is not a valid session handle, or SQLQuery not a string, or ResultTemplate or ParamTemplate not a structure
<DT><EM>(dbi_error) </EM>
<DD>Error from DBMS while preparing SQLQuery.
<DT><EM>(dbi_bad_template) </EM>
<DD>ResultTemplate or ParamTemplate has the wrong arity
</DL>
<H2>Examples</H2>
<PRE>
 make_check_overdraft_limit(Session, Cursor) :-
      % note '?' in SQL in the syntax MySQL uses for placeholders. This may be
      % different in other DBMS
      SQL = "select count(id) from accounts where ID = ? \
                 and balance &lt; overdraft",
      session_sql_prepare_query(Session,a(0),c(0),SQL,1,Cursor).</PRE>
<H2>See Also</H2>
<A HREF="../../lib/dbi/cursor_next_execute-2.html">cursor_next_execute / 2</A>, <A HREF="../../lib/dbi/cursor_next_tuple-2.html">cursor_next_tuple / 2</A>, <A HREF="../../lib/dbi/cursor_all_tuples-2.html">cursor_all_tuples / 2</A>, <A HREF="../../lib/dbi/cursor_N_tuples-4.html">cursor_N_tuples / 4</A>, <A HREF="../../lib/dbi/session_sql-3.html">session_sql / 3</A>, <A HREF="../../lib/dbi/session_sql_query-4.html">session_sql_query / 4</A>
</BODY></HTML>
